Day21. 看看djangoORM與其對應的SQL是否為預期的樣子！

2023 iThome 鐵人賽

DAY 21

自我挑戰組

打掉重練！Django的還債之旅～系列第 21 篇

15th鐵人賽

智

2023-10-06 21:06:36

264 瀏覽

分享至

前言

今天就來看看ORM組成的SQL都長什麼樣子吧，怎麼看呢？當然是用我們昨天設定好的logging或是debug toolbar來看囉！

正題

我們先來修改一下models.py

from django.db import models
from django.utils import timezone

# Create your models here.

class Person(models.Model):
    class Sex(models.IntegerChoices):
        MALE = 0
        FEMALE = 1
        THIRD = 2
    name = models.CharField(max_length=255)
    sex = models.IntegerField(choices=Sex.choices)

    class Meta:
        db_table = "person"



class Todo(models.Model):
    owner = models.ForeignKey(Person, on_delete=models.CASCADE)
    title = models.CharField(max_length=255)
    complete = models.BooleanField(default=False)
    add_date = models.DateField(default=timezone.now)

    class Meta:
        db_table = "todo_list"

然後在settings.py加上templates的位置

TEMPLATES = [
    {
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': ["templates"],  # 這邊是更動處
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [
                'django.template.context_processors.debug',
                'django.template.context_processors.request',
                'django.contrib.auth.context_processors.auth',
                'django.contrib.messages.context_processors.messages',
            ],
        },
    },
]

加完之後在跟manage.py同階層的資料夾下新增一個資料夾templates
然後我們這邊加兩個檔案

index.html
person_todolist.html

內容如下

<!-- index.html -->
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>index</title>
</head>
<body>
    {% for p in person %}
        {{p.id}}
        {{p.name}}
        <a href="person/{{p.id}}"> link </a>
    {% endfor %}    
</body>
</html>

<!-- person_todolist.html -->
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    {% if todo %}
    {% for t in todo %}
        {{t.owner.name}}
        {{t.title}}
        {{t.complete}}
        <br/>
    {% endfor %}
    {% else %}
    no todo list
    {% endif %}
    <button onclick="back()">back</button>
    <script>
        function back(){
            history.back()
        }
    </script>
</body>
</html>

版面我就不管啦ＸＤ
接下來我們去hello_app中的views.py新增

# views.py
from django.shortcuts import render
from django.http import HttpResponse

from .models import Person, Todo
# Create your views here.

def hello(request):
    return HttpResponse("Hello World！")

def index(request):
    p = Person.objects.all()
    return render(request, 'index.html', {"person": p})

def person_todolist(request, id):
    todo = Todo.objects.filter(owner=id)
    return render(request, 'person_todolist.html', {"todo": todo})

接著是urls.py

# urls.py
from django.urls import path
from . import views

urlpatterns = [
    path('hello/', views.hello),
    path('', views.index),
    path('person/<int:id>', views.person_todolist)
]

這樣我們前置作業就完成了！來看看ORM的SQL吧！
首先先在admin新增幾筆資料～
新增完後到127.0.0.1:8000應該就會看到類似下圖這樣，會有你新增的角色person

然後能從console看到
(0.000) SELECT "person"."id", "person"."name", "person"."sex" FROM "person"; args=(); alias=default
也能從debug toolbar看到

接著再點link

可以看到該person的todolist事項～（我這邊沒給他完成，過幾天我們在做個完整版的今天先看看ORM～）
一樣debug toolbar和console都能看到SQL

(0.000) SELECT "todo_list"."id", "todo_list"."owner_id", "todo_list"."title", "todo_list"."complete", "todo_list"."add_date" FROM "todo_list" WHERE "todo_list"."owner_id" = 1; args=(1,); alias=default
(0.000) SELECT "person"."id", "person"."name", "person"."sex" FROM "person" WHERE "person"."id" = 1 LIMIT 21; args=(1,); alias=default
(0.000) SELECT "person"."id", "person"."name", "person"."sex" FROM "person" WHERE "person"."id" = 1 LIMIT 21; args=(1,); alias=default

這邊就有個疑問了！為什麼他明明都是同一個人卻要select兩次呢？
那是因為我們的views.py那邊寫這樣

todo = Todo.objects.filter(owner=id)

這個會導致django重複搜尋，現在只有一兩筆資料所以影響不大，資料量一多DB就要多搜尋好幾次很吃效能的！
所以當有兩個table FK關聯時，這正確的寫法要這樣讓django先去下join

todo = Todo.objects.select_related("owner").filter(owner=id)

那我們再來看改過的～

(0.000) SELECT "todo_list"."id", "todo_list"."owner_id", "todo_list"."title", "todo_list"."complete", "todo_list"."add_date", "person"."id", "person"."name", "person"."sex" FROM "todo_list" INNER JOIN "person" ON ("todo_list"."owner_id" = "person"."id") WHERE "todo_list"."owner_id" = 1; args=(1,); alias=default

摁～只要select一次，完美！